package dao.mysql;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.sql.DataSource;

import model.HolidayDates;
import dao.HolidayDatesDAO;

public class HolidayDatesDAOImpl implements HolidayDatesDAO {
	DataSource dataSource=null;
	Connection con=null;
	PreparedStatement pstmt=null;
	public HolidayDatesDAOImpl()
	{
		con=ConnectionProperties.getConnection();
	}
	@Override
	public void insertHolidayDates(HolidayDates book)throws SQLException {
		// TODO Auto-generated method stub
		
		pstmt=con.prepareStatement("Insert into HolidayDates values(?,?,?)");
		pstmt.setDate(1, book.getHolidayStartDate());
		pstmt.setString(2, book.getHolidayName());
		pstmt.setInt(3, book.getHolidayDuration());
		pstmt.executeUpdate();
	}

	@Override
	public void updateHolidayDates(HolidayDates book)throws SQLException {
		// TODO Auto-generated method stub
		pstmt=con.prepareStatement("update holidaydates set HolidayName=? HolidayDuration=? where HolidayStartDate=?");
		pstmt.setString(1, book.getHolidayName());
		pstmt.setInt(2, book.getHolidayDuration());
		pstmt.setDate(3, book.getHolidayStartDate());
		pstmt.executeUpdate();
		
		
	}

	@Override
	public void deleteHolidayDates(java.sql.Date HolidayStartDate) throws SQLException{
		// TODO Auto-generated method stub
		pstmt=con.prepareStatement("delete from HolidayDates where HolidayStartDate=?");
		pstmt.setDate(1, HolidayStartDate);
		pstmt.executeUpdate();
		
		
	}

	@Override
	public HolidayDates findByHolidayDate(Date HolidayStartDate) throws SQLException{
		// TODO Auto-generated method stub
		HolidayDates hDate=null;
		
		pstmt=con.prepareStatement("select * from HolidayDates where HolidayStartDate=?");
		pstmt.setDate(1, HolidayStartDate);
		ResultSet rs=pstmt.executeQuery();
		if(rs.next())
		{
			hDate=new HolidayDates();
			hDate.setHolidayStartDate(rs.getDate("HolidayStartDate"));
			hDate.setHolidayName(rs.getString("HolidayName"));
			hDate.setHolidayDuration(rs.getInt("HolidayDuration"));
		}
		return hDate;
	}

	@Override
	public ArrayList<HolidayDates> findAllHolidays() throws SQLException{
		// TODO Auto-generated method stub
		ArrayList<HolidayDates> HDates=new ArrayList<HolidayDates>();
		pstmt=con.prepareStatement("select * from HolidayDates");
		
		ResultSet rs=pstmt.executeQuery();
		while(rs.next())
		{
			HolidayDates hDate=new HolidayDates();
			hDate.setHolidayStartDate(rs.getDate("HolidayStartDate"));
			hDate.setHolidayName(rs.getString("HolidayName"));
			hDate.setHolidayDuration(rs.getInt("HolidayDuration"));
			HDates.add(hDate);
		}
		return HDates;
	}
	@Override
	public ArrayList<HolidayDates> findHolidays(int day,int month)
	{
		ArrayList<HolidayDates> holidayDate=new ArrayList<HolidayDates>();
		try {
			pstmt=con.prepareStatement("select holidaystartdate,holidayduration from HolidayDates where Day(holidaystartdate)<=? and Month(holidaystartdate)=?");
			pstmt.setInt(1, day);
			pstmt.setInt(2, month);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next())
			{
				HolidayDates holid=new HolidayDates();
				holid.setHolidayDuration(rs.getInt("holidayduration"));
				holid.setHolidayStartDate(rs.getDate("holidaystartdate"));
				holidayDate.add(holid);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return holidayDate;
	}
}
